  create  table  #Tmp
  (
Customer_id  varchar(10),
ctype  nvarchar(50),
val      numeric(18,2)
  )
  
DECLARE  @i  INT=1
DECLARE  @Cnt  INT  
DECLARE  @entity  VARCHAR(50)
DECLARE  @StartDate    datetime  ='2018-12-04  20:00'
DECLARE  @date  date
SET  @date  =    CONVERT(date,  @StartDate    )
  
--取店铺资料  
SELECT  IDENTITY(int,1,1)  AS  id,Customer_id,Customer_na  INTO  #customer	FROM    r_entity_target  A  
inner  join    [regent].dbo.customer  B  on  A.entity_code=B.Customer_id  and  target_year=YEAR(@StartDate)
where  DealInPattern  is  null  
SET  @Cnt=@@ROWCOUNT
  
WHILE  @I<=@Cnt
BEGIN  
SELECT  @entity=Customer_id  FROM  #customer  where  id=@i

SELECT  h.CheckID,  SUM(d.quantity)  as  qty  ,  COUNT(distinct  h.CheckID)  as  cnt  ,  SUM(d.Quantity  *  d.DiscountPrice)  as  amt  ,  case  when  isnull(h.VIP_Card  ,'')  <>''  then    1  else  0  end  as  isvip,
isnull(h.VIP_Card,h.CheckID)  as  kd
INTO  #t1
FROM  [regent].dbo.[check]  h  
INNER  join  [regent].dbo.[checkGoods]  d  on  h.CheckID  =  d.CheckID
WHERE  Customer_ID  =  @entity  and  h.CheckDate  =  @date  
GROUP  by  h.checkid  ,  h.VIP_Card  
  
  
DECLARE  @日目标  numeric(38,2)=0
DECLARE  @实收    numeric(38,2)=0
DECLARE  @成交单数    int=0
DECLARE  @客单价  numeric(38,2)=0
DECLARE  @日数量    numeric(38,2)=0  
DECLARE  @月目标  numeric(38,2)=0
DECLARE  @月累计  numeric(38,2)=0
DECLARE  @库存  int=0
DECLARE  @库存2  int=0
DECLARE  @最近7天  numeric(38,2)=0
DECLARE  @sql  nvarchar(1000)
DECLARE  @会员  int=0
DECLARE  @累计会员  int=0

SELECT  @日目标=date_target  FROM  r_entity_open  where  entity_code=@entity  and  open_date=@date


set  @sql='SELECT  @月目标=month'+  right(  convert(varchar(6),GETDATE(),112),2)    +'  FROM  r_entity_target  where  entity_code=@entity  and  target_year=year(@date)'
exec  sp_executesql  @sql,N'@entity  varchar(50),  @date  date  ,@月目标  numeric(38,2)  output',@entity,@date,@月目标  output


SELECT  @实收=sum(amt),@成交单数=SUM(cnt),@客单价=SUM(amt)/count(kd),@日数量=SUM(qty)  FROM  #t1
  
SELECT    @会员=COUNT(distinct  VIP_Card)
FROM  [regent].dbo.[check]  h  
INNER  join  [regent].dbo.[checkGoods]  d  on  h.CheckID  =  d.CheckID
WHERE  Customer_ID  =  @entity  and  h.CheckDate  =  @date  and  d.goods_no  ='9999'

SELECT  @累计会员=  COUNT(1)      FROM  [regent].dbo.CustomerVIP  where  vipgrade=N'年费会员'  and  Customer_ID=@entity
  
SELECT  @月累计=SUM(d.Quantity  *  d.DiscountPrice)  
FROM  [regent].dbo.[check]  h  
INNER  join  [regent].dbo.[checkGoods]  d  on  h.CheckID  =  d.CheckID
WHERE  Customer_ID  =  @entity  and  h.CheckDate>=dateadd(DAY,1-day(@date),@date)  and  h.CheckDate  <=  @date    
      
SELECT  
@库存=SUM(case  when  Category  in  ('连衣裙','上装','套装','下装')  then  quantity  else  0  end),
@库存2=SUM(case  when  isnull(Category,'')  not  in  ('连衣裙','上装','套装','下装')  then  quantity  else  0  end)
FROM  [regent].dbo.CStock  A  inner  join  [regent].dbo.goods  B  on  A.Goods_No=B.Goods_no
where  Customer_Id=@entity  and  A.Goods_No  not  in('9999','8888')


SELECT  @最近7天=SUM(d.Quantity)  
FROM  [regent].dbo.[check]  h  
INNER  join  [regent].dbo.[checkGoods]  d  on  h.CheckID  =  d.CheckID
inner  join  [regent].dbo.Goods  E  on  d.Goods_No=E .Goods_no
WHERE Customer_ID = @entity and checkdate>=dateadd(day,-7,@date) and checkdate<@date   and Category in ('连衣裙','上装','套装','下装')

insert into #Tmp values(@entity,'日目标',@日目标)	
,(@entity,'月目标',@月目标)
,(@entity,'实收',@实收)
,(@entity,'成交单数',@成交单数)
,(@entity,'连带率',@日数量/nullif(@成交单数,0))
,(@entity,'客单价',@客单价)
,(@entity,'完成率%',100.0*isnull(@实收,0)/nullif(@日目标,0))
,(@entity,'新增会员',@会员)
,(@entity,'累计会员',@累计会员)
,(@entity,'月累计',@月累计)	  	    
,(@entity,'累计完成率%',100.0*isnull(@月累计,0)/nullif(@月目标,0))	
,(@entity,'库存数量(衣服)',@库存)	
,(@entity,'库存数量(配饰)',@库存2)	 
,(@entity,'流转周',isnull(@库存,0)/nullif(@最近7天,0))	
  
SET @i=@i+1
drop table #t1
END 

SELECT 
MONTH(@StartDate) As 月,day(@StartDate) As 日,
DATENAME(WEEKDAY,@StartDate) As 星期,
CONVERT(VARCHAR(5),@StartDate,108) As 时,* FROM (SELECT A.Customer_id,A.Customer_na,B.ctype,B.val
FROM #customer A left join #Tmp B on A.Customer_id=B.Customer_id)
A
pivot
(
max(val)
for ctype in(日目标,月目标,实收,成交单数,连带率,客单价,[完成率%],新增会员,累计会员,月累计,[累计完成率%],[库存数量(衣服)],[库存数量(配饰)],流转周)
)p

DROP TABLE #customer
DROP TABLE #Tmp
 